"""
Case Type   : 系统内部使用工具
Case Name   : 范围分区表结合local索引
Create At   : 2022/03/03
Owner       : zou_jialiang050
Description :
    1.构建数据
    2.清理环境
Expect      :
    1.构建成功
    2.成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.Common import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

Constant = Constant()


class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pricomsh = CommonSH("PrimaryDbUser")
        self.primary_node = Node('PrimaryDbUser')
        self.target_path = os.path.join(macro.DB_BACKUP_PATH, "source_data")

    def test_ustore(self):
        text = f"----step1:范围分区表结合local索引:expect:成功;----"
        self.log.info(text)
        file_lis = ['tab_ap_join_08.txt']
        for file in file_lis:
            self.common.scp_file(self.primary_node, f"{file}", self.target_path)

        sql1 = f"""drop table if exists tab_ap_join_08;
        create table tab_ap_join_08(
                wd_tinyint       tinyint not null,    
          wd_smallint      smallint not null, 
          wd_int        integer not null ,  
          wd_bigint       bigint,  
          wd_numeric       numeric not null, 
          wd_real       real,
          wd_double       double precision,
          wd_decimal          decimal,
          wd_varchar        varchar(100) not null,
          wd_char       char(30),
          wd_nvarchar2      nvarchar2(100),
          wd_text       varchar(2000),
          wd_timestamptz  varchar(100), 
          wd_timestamp  varchar(100), 
          wd_date       date,  
          wd_time       varchar(100),  
          wd_timetz   varchar(100), 
          wd_interval      varchar(100) not null, 
          wd_smalldatetine varchar(100), 
          wd_text1            varchar(2000),
          wd_int1             integer,
          wd_varchar1         varchar(100),
          wd_num           numeric,
          wd_num1       numeric,
          wd_char1            char(20),
          wd_char2            char(20),
          wd_date4       date not null,
          wd_date5       date not null   
        )
        with (storage_type=ustore) partition by range (wd_date4)
        (
        partition tab_ap_join_08_1 values less than ('2014-12-01'),
        partition tab_ap_join_08_2 values less than ('2020-12-01'),
        partition tab_ap_join_08_3 values less than ('2025-12-01'),
        partition tab_ap_join_08_4 values less than ('2030-12-01'),
        partition tab_ap_join_08_5 values less than ('2035-12-01'),
        partition tab_ap_join_08_6 values less than ('2040-12-01'),
        partition tab_ap_join_08_7 values less than ('2045-12-01'),
        partition tab_ap_join_08_8 values less than ('2050-12-01'),
        partition tab_ap_join_08_9 values less than ('2055-12-01'),
        partition tab_ap_join_08_10 values less than (maxvalue)
        );
        
        copy tab_ap_join_08 from 
        '{os.path.join(self.target_path, 'tab_ap_join_08.txt')}' delimiters  ',';
        
        create index tab_ap_join_08_01 on tab_ap_join_08(wd_smallint) local;
        create index tab_ap_join_08_02 on tab_ap_join_08(wd_int) local;
        """

        res1 = self.pricomsh.execut_db_sql(sql1)
        self.log.info(res1)

        self.assertIn('CREATE TABLE', res1, 'sql1执行失败')
        self.assertIn('COPY 3000', res1, 'sql1执行失败')
        self.assertIn('CREATE INDEX', res1, 'sql1执行失败')

    def tearDown(self):
        text = f"----step2:清理环境:expect:成功;----"
        self.log.info(text)
        del_flag = False
        del_cmd = f'rm -rf {self.target_path};' \
                  f'if [ -d {self.target_path} ]; ' \
                  f'then echo "exists";else echo "not exists"; fi'
        for num in range(10):
            del_res = self.common.get_sh_result(self.primary_node, del_cmd)
            if 'not exists' in del_res:
                del_flag = True
                break
        self.assertTrue(del_flag, '文件删除失败')
        self.log.info(f'----{os.path.basename(__file__)} finish----')

